package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;


import com.ws.shop.dao.AccessoryDao;
import com.ws.shop.entity.Accessory;
import com.ws.shop.util.JdbcUtil;

@Repository(value="accessoryDao")
public class AccessoryDaoImpl extends BaseDaoImpl implements AccessoryDao {
	
	private static Logger log = Logger.getLogger(AccessoryDaoImpl.class);
	
	public List<Accessory> getAccessoryByRelationId(long relationid){
		String s = "select ac.type,ac.title,ac.path,ac.size,ac.thumbnail,ac.uploadtime from jvf_accessory_relation as ar join jvf_accessory as ac on ar.accessoryid = ac.id  where ar.relationid = "+relationid+" and ar.tables = 'Sunpic'";
		return this.query(s, new ResultSetExtractor<List<Accessory>>(){
			public List<Accessory> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				List<Accessory> list = new LinkedList<Accessory>();
				Accessory a = null;
				while(rs.next()){
					a = new Accessory();
					a.setType(rs.getString(1));
					a.setTitle(rs.getString(2));
					a.setPath(rs.getString(3));
					a.setSize(rs.getLong(4));
					a.setThumbnail(rs.getString(5));
					a.setUploadtime(rs.getTimestamp(6));
					list.add(a);
				}
				return list;
			}
		});
	}

	final String SAVE_SQL = "insert into jvf_accessory(type,title,path,size,thumbnail,uploadtime) values(?,?,?,?,?,now())";
	public Object[] saveOrUpdate(final Accessory accessory) {
		return this.execute(new ConnectionCallback<Object[]>(){
			public Object[] doInConnection(Connection conn)
					throws SQLException, DataAccessException {
				PreparedStatement ps = null;
				ResultSet rs = null;
				try{
					ps = conn.prepareStatement(SAVE_SQL, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1,accessory.getType());
					ps.setString(2, accessory.getTitle());
					ps.setString(3, accessory.getPath());
					ps.setLong(4, accessory.getSize());
					ps.setString(5, accessory.getThumbnail());
					ps.executeUpdate();
					rs = ps.getGeneratedKeys();
					if(rs.next()){
						return new Object[]{0,rs.getInt(1)+""};
					}
					return new Object[]{1,"保存图片信息失败"};
				}finally{
					JdbcUtil.close(rs, ps, null);
				}
			}
		});
	}
	
	final String deleteById = "delete from jvf_accessory where id=?";
	public void deleteById(int id) {
		this.update(deleteById, id);
	}
	
	final String findAccessoryById="select type,title,path,size,thumbnail,uploadtime from jvf_accessory where id=?";
	public Accessory findAccessoryById(final int id) {
		return this.query(findAccessoryById, new ResultSetExtractor<Accessory>(){
			public Accessory extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(!rs.next())
					return null;
				Accessory a = new Accessory();
				a.setId(id);
				a.setType(rs.getString(1));
				a.setTitle(rs.getString(2));
				a.setPath(rs.getString(3));
				a.setSize(rs.getLong(4));
				a.setThumbnail(rs.getString(5));
				a.setUploadtime(rs.getTimestamp(6));
				return a;
			}
		}, id);
	}
	
	
	public int winersayUpimg(final long uid,final long oid,final long  accessoryid){
		return (Integer) this.execute(new ConnectionCallback() {
			@SuppressWarnings("unchecked")
			public Object doInConnection(Connection connect)
					throws SQLException, DataAccessException {
				Statement stmt = null;
				ResultSet rs = null;
				try {
					connect.setAutoCommit(false);
					String s = "select uid,aid,name,state from jvf_order where id = "+ oid + " for update";
					stmt =  connect.createStatement();
					rs = stmt.executeQuery(s);
					if (rs.next()) {
						long uid =rs.getLong(1);
						long aid =rs.getLong(2);
						String name =rs.getString(3);
						JdbcUtil.close(rs, stmt, null);
						s = "select s.id ,s.uid,s.oid ,s.name ,s.aid ,s.speech ,s.comments ,s.state ,s.addtime ,s.reward ,s.link from jvf_sunpic as s where s.uid = "+uid+" and s.oid = "+oid;
						stmt =  connect.createStatement();
						rs = stmt.executeQuery(s);
						long sunpicid = 0;
						int supdate = 0;
						if(rs.next()){
							sunpicid = rs.getLong(1);
							JdbcUtil.close(rs, stmt, null);
						}else{
							s = "insert into jvf_sunpic(uid,oid,name,aid,speech) values("+uid+","+oid+",'"+name+"',"+aid+")";
							stmt =  connect.createStatement();
							supdate = stmt.executeUpdate(s);
							if(supdate==0){
								connect.rollback();
								return 2;
							}
							JdbcUtil.close(rs, stmt, null);
						}
						s = "insert into jvf_accessory_relation(accessoryid,relationid,table)values('"+accessoryid+"','"+ sunpicid+"','Sunpic')";
						stmt =  connect.createStatement();
						int accupdate = stmt.executeUpdate(s);
						JdbcUtil.close(rs, stmt, null);
						if(accupdate>0){
							connect.commit();
							return 0;
						}else{
							connect.rollback();
							return 3;
						}
					} else {
						connect.rollback();
						return 1;// 订单不存在
					}
				} catch (Exception e) {
					e.printStackTrace();
					connect.rollback();
					log.error(e.getMessage(), e);
					return -1;
				} finally {
					JdbcUtil.close(rs, stmt, null);
				}
			}
		});
		 
	}
	
	
	
}
